# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import openpyxl
import pymysql
from scrapy import signals

from pydispatch import dispatcher
from openpyxl import Workbook


class DbPipeline:

    def __init__(self):
        self.connect = pymysql.Connect(
            host='127.0.0.1',
            port=13306,
            user='root',
            password='123456',
            database='movie',
            charset='utf8',

        )
        self.cursor = self.connect.cursor()
        print('连接上mysql数据库')

    def process_item(self, item, spider):
        title = item.get('title', '')
        href = item.get('href', '')
        rank = item.get('spider_rank', 0)
        num = item.get('num', '')
        subject = item.get('Introduction', '')
        sql = "insert into tb_movie (title, href ,spider_rank,num ,Introduction) values ('{}' ,'{}' , '{}','{}','{}')".format(
            title,
            href,
            rank,
            num,
            subject)
        print(sql)
        # title = item.get('title', '')
        # rank = item.get('spider_rank', 0)
        # subject = item.get('Introduction', '')
        # sql = "insert into tb_top_movie (title, spider_rank, Introduction) values ('{}' ,'{}' , '{}')".format(title,
        #                                                                                                       rank,
        #                                                                                                       subject)
        # print(sql)

        try:
            self.connect.ping(
                True)  # mysql  一般只有8个小时有效时间，这个是在运行sql的时候，去试着连接mysql如果连接上就会执行sql，如果mysql8小时失效就会连接mysql在执行sql1
            self.cursor.execute(sql)
            self.connect.commit()
            return item
        except Exception as e:
            print(f'存入数据失败 错误:{e} ')
            print(f'失败sql: {sql}')

    def close_spider(self, spider):
        self.cursor.close()
        self.connect.close()
